package com.hysoft.controller;



import com.alibaba.fastjson.JSONObject;
import com.hysoft.service.OutDataFromOracleToAccess;
import com.hysoft.util.JdbcUtil;
import com.hysoft.util.LogUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;


@Controller
@RequestMapping(value = "/ManuallyExportController")
public class ManuallyExportController {

//    @Autowired
//    private OutDataFromOracleToAccessImpl outDataFormOracleToAccess;

    @Autowired
    private OutDataFromOracleToAccess outDataFormOracleToAccess;

    @Autowired
    private JdbcUtil jdbcUtil;

    /**
     * @param host 主机地址
     * @param port  oracle端口
     * @param username oracle登陆口令,不论大小写，直接统一转换为大写
     * @param password oracle口令密码
     * @param SID 服务名
     * 返回所有的表名
     */

    @RequestMapping(value = "/findAllTablesList")
    @ResponseBody
    public List<String> getAllTables(String host, String port, String username, String password, String SID){
//        Connection oracleConn = jdbcUtil.getOracleConnection("127.0.0.1","1521","lyb","lyb");
//        List<String> allTables = outDataFormOracleToAccess.allTables(oracleConn,"LYB");
        Connection oracleConn = jdbcUtil.getOracleConnection(host,port,SID,username,password);
        List<String> allTables = outDataFormOracleToAccess.allTables(oracleConn,username.toUpperCase());
//        List<String> finalTables = new ArrayList<>();
//        finalTables = WrapList2json.list2json(allTables);
        return allTables;
    }

    @ResponseBody
    @RequestMapping(value = "/sd")
    public List<String> dfe(){
        List<String> sdfd=new ArrayList<>();
        sdfd.add("dsg");
        return sdfd;
    }

    /**
     * 获得选中数据表的所有字段
     * @param SJB 数据表
     * @param host 主机地址
     * @param port  oracle端口
     * @param SID 服务名
     * @param username oracle登陆口令
     * @param password oracle口令密码
     * @return 当前表中所有字段
     */

    @RequestMapping(value = "/findAllFieldsList")
    @ResponseBody
    public List<String> getAllFields(String SJB,String host,String port,String SID,String username,String password){
//        Connection oracleConn = jdbcUtil.getOracleConnection("127.0.0.1","1521","lyb","lyb");
//        List<String> allFields = outDataFormOracleToAccess.allFields("JOBS",oracleConn);
        Connection oracleConn = jdbcUtil.getOracleConnection(host,port,SID,username,password);
        List<String> allFields = outDataFormOracleToAccess.allFields(SJB,oracleConn);
        return allFields;
    }

    /**
     * 按条件手动导出
     * @param str 前端传过来的json字符串{"":"","":""} key是表名 value是处理条件
     * @param host 主机地址
     * @param port  oracle端口
     * @param username oracle登陆口令,不论大小写，直接统一转换为大写
     * @param password oracle登陆密码
     * @param path access导出文件的绝对路径
     * @param depart 单位部门
     * @return
     * @throws ClassNotFoundException
     */

    @RequestMapping(value = "/exportWithCondition")
    @ResponseBody
    public String exportWithCondition(String str,String host,String port,String SID,String username,String password,String path,String depart) throws ClassNotFoundException {

//        String conditionSql = "select * from JOBS where JOB_ID = '1' ";
//        Connection accessConn = jdbcUtil.getAccessConnection("E:\\Database1.mdb");
//        Connection oracleConn = jdbcUtil.getOracleConnection("127.0.0.1","1521","lyb","lyb");
//        outDataFormOracleToAccess.Condition("JOBS",conditionSql,accessConn,oracleConn,"LYB");
//        outDataFormOracleToAccess.Condition("RYXX_XXTX",conditionSql,accessConn,oracleConn,username);
        String logInfo = "";
        logInfo = LogUtil.getCurrentTime()+LogUtil.getOracleDBInfo("oracle",host,port,SID,username)+LogUtil.getSectionAndAccessPath("新疆油田",path);
        try {
            LogUtil.inserCorrectLog("Out",logInfo,"ok.txt");
            LogUtil.inserCorrectLog("Out",logInfo,"error.txt");
        } catch (IOException e) {
            e.printStackTrace();
        }
        String conditionSql = "";
        Map json = (Map)JSONObject.parse(str);
        Connection oracleConn = jdbcUtil.getOracleConnection(host, port, SID ,username, password);
        Connection accessConn = jdbcUtil.getAccessConnection(path);
        for (Object map:json.entrySet()){
            if (((Map.Entry)map).getValue().equals(null)||((Map.Entry)map).getValue().equals("")||((Map.Entry)map).getValue()==null||((Map.Entry)map).getValue()==""){
                conditionSql = " select * from "+((Map.Entry)map).getKey();
                System.out.println(conditionSql = " select * from " + ((Map.Entry) map).getKey());
            }else{
                conditionSql = " select * from "+((Map.Entry)map).getKey()+" where "+((Map.Entry)map).getValue();
                System.out.println(conditionSql = " select * from " + ((Map.Entry) map).getKey() + " where " + ((Map.Entry) map).getValue());
            }
            //oracle 只要密码确定，不论登录口令是大写还是小写，或者是大小写混合都能登录，为了操作方便，直接toUpperCase全转化为大写
            outDataFormOracleToAccess.condition(((Map.Entry)map).getKey().toString(),conditionSql,accessConn,oracleConn,username.toUpperCase());
        }
        return "操作完毕";
    }

    /**
     * http://localhost:8888/ytsjcjxtclent_Web_exploded/YTSJCJXT/ManuallyExportController/exportFromOracleToAccess?host=192.168.0.134&port=1521&SID=ORCL&userName=WL_SCK&passwordWL_SCK&path=D:\access\test3.accdb&str={"CLASS":""}
      * @param host
     * @param port
     * @param SID
     * @param userName
     * @param password
     * @param path
     * @param
     * @return
     * @throws ClassNotFoundException
     * @throws SQLException
     */


    @RequestMapping(value = "/exportFromOracleToAccess")
    @ResponseBody
    public String exportFromOracleToAccess(String host,String port,String SID,String userName,String password, String path) throws ClassNotFoundException, SQLException {
 //   public String exportFromOracleToAccess(String host,String port,String userName,String password,String path,String conditionSql) throws ClassNotFoundException, SQLException {
//        JdbcUtil jdbcUtil = new JdbcUtil();
//        jdbcUtil.AccessJdbcUtil("D:\\access\\test1.accdb");
//        jdbcUtil.OracleJdbcUtil("192.168.0.134","1521","WL_SCK","WL_SCK");
//        Connection accessConn = jdbcUtil.getAccessConnection("D:\\access\\test1.accdb");
//        Connection oracleConn = jdbcUtil.getOracleConnection("192.168.0.134","1521","ORCL","WL_SCK","WL_SCK");
        //outDataFormOracleToAccess.createTable(oracleConn,accessConn,"WL_SCK");
       // Connection oracleConn = jdbcUtil.getOracleConnection("192.168.0.134","1521","WL_SCK","WL_SCK");
        String logInfo = "";
        logInfo = LogUtil.getCurrentTime()+LogUtil.getOracleDBInfo("oracle",host,port,SID,userName)+LogUtil.getSectionAndAccessPath("新疆油田",path);
        try {
            LogUtil.inserCorrectLog("Out",logInfo,"ok.txt");
            LogUtil.inserCorrectLog("Out",logInfo,"error.txt");
        } catch (IOException e) {
            e.printStackTrace();
        }
        String str = "{'CLASS':''}";
        String conditionSql = "";
        Map json = (Map) JSONObject.parse(str);
        Connection oracleConn = jdbcUtil.getOracleConnection("192.168.0.134","1521","ORCL","WL_SCK","WL_SCK");
        Connection accessConn = jdbcUtil.getAccessConnection("D:\\access\\test1.accdb");
        for (Object map:json.entrySet()){
            if (((Map.Entry)map).getValue().equals(null)||((Map.Entry)map).getValue().equals("")||((Map.Entry)map).getValue()==null||((Map.Entry)map).getValue()==""){
                conditionSql = " select * from "+"WL_SCK"+"."+((Map.Entry)map).getKey();
                System.out.println(conditionSql);
            }else{
                conditionSql = " select * from "+"WL_SCK"+"."+((Map.Entry)map).getKey()+" where "+((Map.Entry)map).getValue();
                System.out.println(conditionSql);
            }
            //oracle 只要密码确定，不论登录口令是大写还是小写，或者是大小写混合都能登录，为了操作方便，直接toUpperCase全转化为大写
            outDataFormOracleToAccess.createTable(((Map.Entry)map).getKey().toString(),oracleConn,accessConn,"WL_SCK",conditionSql);
        }

        return "success";
    }

}
